//  -------------------- iSharePoint -------------------- //
// 
//  Company:	IT Complex, LLC
//  Project:	Smart.Common
//  File name:	MetaSqlObject.cs
//  Developer:	Solomatov Igor
//  Created:	15.04.2012
// 
//  -------------------- iSharePoint -------------------- //

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.SharePoint;

namespace Smart.Common
{
	/// <summary>
	/// Metadata for SQL Server object
	/// </summary>
	[DebuggerDisplay("MetaSqlObject Name={Name} Title={Title}")]
	public class MetaSqlObject : MetaList
	{
		/// <summary>
		/// Return guid for object id
		/// </summary>
		private static Guid GetObjectGuid(int id)
		{
			return new Guid((uint) id, 0xAAF8, 0x41AD, 0, 0, 0, 0, 0, 0, 0, 0);
		}

		/// <summary>
		/// Object ID
		/// </summary>
		public int ObjectId { get; set; }

		/// <summary>
		/// Table type
		/// </summary>
		public SqlObjectType ObjectType { get; set; }

		/// <summary>
		/// Primary key column name
		/// </summary>
		public string PrimaryKey { get; set; }

		/// <summary>
		/// Title key column name
		/// </summary>
		public string TitleKey { get; set; }

		/// <summary>
		/// Stored Procedure for adding
		/// </summary>
		public string SPAdd { get; set; }

		/// <summary>
		/// Parameters of stored Procedure for adding
		/// </summary>
		public List<MetaSqlColumn> SPAddParameters { get; set; }

		/// <summary>
		/// Stored Procedure for updating
		/// </summary>
		public string SPUpdate { get; set; }

		/// <summary>
		/// Parameters of stored Procedure for updating
		/// </summary>
		public List<MetaSqlColumn> SPUpdateParameters { get; set; }

		/// <summary>
		/// Stored Procedure for deleting
		/// </summary>
		public string SPDelete { get; set; }

		/// <summary>
		/// Parameters of stored Procedure for deleting
		/// </summary>
		public List<MetaSqlColumn> SPDeleteParameters { get; set; }

		/// <summary>
		/// Primary key field
		/// </summary>
		[Obsolete("in StorageProcedure mode no metadata about result set")]
		public MetaField PrimaryKeyField
		{
			get { return Field(PrimaryKey); }
		}

		/// <summary>
		/// Stored procedure ResultCode column name
		/// </summary>
		public string ResultCodeKey { get; set; }

		/// <summary>
		/// Stored procedure ResultDescription column name
		/// </summary>
		public string ResultDescriptionKey { get; set; }

		/// <summary>
		/// Metadata for SQL Server object
		/// </summary>
		public MetaSqlObject(int id, string name, SqlObjectType objectType, string primaryKey, string titleKey, MetaScope scope)
			: base(GetObjectGuid(id), name, name, scope)
		{
			ObjectId = id;
			ObjectType = objectType;
			PrimaryKey = primaryKey;
			TitleKey = titleKey;
		}

		/// <summary>
		/// Fill cache info
		/// </summary>
		public override void LoadFields(object source)
		{
			var conn = (SqlConnection) source;
			var isSP = ObjectType == SqlObjectType.SP;

			using (var cmd = conn.CreateCommand())
			{
				cmd.CommandText = string.Format(isSP ? SqlConst.AllParametersFor : SqlConst.AllColumnsFor, ObjectId);
				cmd.CommandTimeout = SqlConst.FastCommandTimeout;
				cmd.CommandType = CommandType.Text;

				using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SequentialAccess))
				{
					while (reader.Read())
					{
						var cid = reader.GetInt32(0);
						var cname = isSP ? reader.GetString(1).TrimStart("@") : reader.GetString(1);
						var ctype = reader.GetString(2).ToLower();

						var cIsIdentity = !isSP && reader.GetBoolean(3);
						var cIsComputed = !isSP && reader.GetBoolean(4);

						if (ctype.Out("text", "ntext", "varchar", "nvarchar", "int", "float", "decimal", "bit", "datetime", "uniqueidentifier"))
							continue;

						SPFieldType ft;

						switch (ctype)
						{
							case "text":
							case "ntext":
							case "varchar":
							case "nvarchar":
								ft = SPFieldType.Text;
								break;

							case "int":
								ft = SPFieldType.Integer;
								break;

							case "float":
							case "decimal":
								ft = SPFieldType.Number;
								break;

							case "bit":
								ft = SPFieldType.Boolean;
								break;

							case "datetime":
								ft = SPFieldType.DateTime;
								break;

							case "uniqueidentifier":
								ft = SPFieldType.Guid;
								break;

							default:
								throw new NotSupportedException("ColumnType");
						}

						var mf = new MetaSqlColumn(cid, cname, ft, this)
						         	{
						         		IsIdentity = cIsIdentity,
						         		IsComputed = cIsComputed
						         	};

						FieldIDs[mf.Name] = mf.ID;
						FieldIDs[mf.ColumnID.ToString()] = mf.ID;
						FieldIDs[mf.ID.ToString("D")] = mf.ID;
						FieldIDs[mf.ID.ToString("B")] = mf.ID;

						Fields[mf.ID] = mf;
					}
				}
			}
		}

		/// <summary>
		/// Fill stored procedure parameters info
		/// </summary>
		public List<MetaSqlColumn> LoadParameters(object source, string spName)
		{
			if (spName.IsNullOrEmpty())
				throw new ArgumentNullException("spName");

			var parameters = new List<MetaSqlColumn>();

			var conn = (SqlConnection) source;

			using (var cmd = conn.CreateCommand())
			{
				cmd.CommandText = string.Format(SqlConst.AllParametersForSP, spName.IncapsulateSql());
				cmd.CommandTimeout = SqlConst.FastCommandTimeout;
				cmd.CommandType = CommandType.Text;

				using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SequentialAccess))
				{
					while (reader.Read())
					{
						var cid = reader.GetInt32(0);
						var cname = reader.GetString(1).TrimStart("@");
						var ctype = reader.GetString(2).ToLower();

						if (ctype.Out("text", "ntext", "varchar", "nvarchar", "int", "float", "decimal", "bit", "datetime", "uniqueidentifier"))
							continue;

						SPFieldType ft;

						switch (ctype)
						{
							case "text":
							case "ntext":
							case "varchar":
							case "nvarchar":
								ft = SPFieldType.Text;
								break;

							case "int":
								ft = SPFieldType.Integer;
								break;

							case "float":
							case "decimal":
								ft = SPFieldType.Number;
								break;

							case "bit":
								ft = SPFieldType.Boolean;
								break;

							case "datetime":
								ft = SPFieldType.DateTime;
								break;

							case "uniqueidentifier":
								ft = SPFieldType.Guid;
								break;

							default:
								throw new NotSupportedException("ColumnType");
						}

						var mf = new MetaSqlColumn(cid, cname, ft, this);

						parameters.Add(mf);
					}
				}
			}

			return parameters;
		}
	}
}